﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using NRemedy;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class User_X : Basic, IUser_X
    {
        private const string selectSiteIDByName = "SELECT a.ID,b.SiteID,s.Alias SiteAlias FROM user_x a INNER JOIN usersite b ON a.ID=b.userID " +
"INNER JOIN sitebranch s ON b.siteid=s.id WHERE a.name=:name";
        private const string selectAllSiteCount = "select count(*) from sitebranch";
        private const string selectAllUserCount = "select count(*) from User_X";
        private const string selectGetSiteBranchByID = "select s.* from user_x a inner join usersite b on b.userid=a.ID inner join sitebranch s on b.siteid=s.ID where a.ID=:ID";
        private const string sqlUpdateRoleByUserIDDUpdate = "update  user_x set department=:department,position=:position,disabled=:disabled,IsAdmin=:IsAdmin,IsLeader=:IsLeader,AccessUS=:AccessUS,AccessITSM=:AccessITSM,AccessGJ=:AccessGJ where id=:userID";
        private const string sqlUpdateRoleByUserIDDelete = "delete from usersite where userid=:userID";
        private const string sqlUpdateRoleByUserIDInsert = "insert into usersite (userid,siteid) values(:userID,:siteID)";

        private const string sqlUpdateRole_GJByUserIDDelete = "delete from usersite_gj where userid=:userID";
        private const string sqlUpdateRole_GJByUserIDInsert = "insert into usersite_gj (userid,siteid) values(:userID,:siteID)";

        public List<UserAndSiteModel> GetSiteIDByName(string name)
        {
            return ModelHelper<UserAndSiteModel>.GetModel_By_DirectSql(selectSiteIDByName, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="name",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=20,Value=name}
                 },
     oda);
        }


        public Decimal GetCountOfTheAllSite()
        {
            return ModelHelper<User_X>.GetModelCount_By_DirectSql(selectAllSiteCount, null, oda);
        }


        public User_XModel GetUser_XByName(string name)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="Name", OracleDbColumnType=OracleDbType.NVarchar2,OracleDbColumnSize=20,
                        OracleDbColumnValue=name,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, null, null, null, oda, null).FirstOrDefault();
        }

        public List<User_XModel> GetListPageAndSortUser_X(out Decimal totalRecords, int pageSize = -1, int pageIndex = -1,
            string sortName = "DisplayName", string sortDirection = "Ascending"/*Ascending,Descending*/, string name = "", string displayname = "")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sortName, sortDirection);
            List<QueryParameter> attParam = new List<QueryParameter>();
            string sqlStr = "select count(*) from User_X where 1=1 and Disabled=0 ";
            List<OracleParameter> param = new List<OracleParameter>();
            if (!string.IsNullOrEmpty(name))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Name",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 20,
                    OracleDbColumnValue = name,
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where
                });
                sqlStr += " and Name like :Name ";
                param.Add(new OracleParameter("Name", OracleDbType.NVarchar2, "%" + name + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(displayname))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "DisplayName",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 20,
                    OracleDbColumnValue = displayname,
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where
                });
                sqlStr += " and DisplayName like :DisplayName ";
                param.Add(new OracleParameter("DisplayName", OracleDbType.NVarchar2, "%" + displayname + "%", ParameterDirection.Input));
            }

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Disabled",
                OracleDbColumnType = OracleDbType.Decimal,
                OracleDbColumnValue = 0,
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });

            totalRecords = ModelHelper<User_XModel>.GetModelCount_By_DirectSql(sqlStr, param, oda);

            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda, null);
        }



        public List<SiteBranchModel> GetSiteBranchByID(decimal ID)
        {
            return ModelHelper<SiteBranchModel>.GetModel_By_DirectSql(selectGetSiteBranchByID, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="ID",OracleDbType=OracleDbType.Decimal,Value=ID}
                 },
      oda);
        }


        public User_XModel GetUserByID(decimal ID)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, null, null, null, oda, null).FirstOrDefault();

        }

        public SFUser_XModel GetUserByIDSF(decimal ID)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            var model = ModelHelper<SFUser_XModel>.GetModel_By_QueryParams(attParam, null, null, null, oda, null,
             new List<string>() { "ID", "UserIdentify", "Name", "DisplayName", "Department", "Position", "Disabled", "IsAdmin", "CreateTime", "LastModifiedTime", "Company", "IsLeader", "AccessUS", "AccessITSM", "AccessGJ" }).FirstOrDefault();

            using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString))
            {
                conn.Open();
                OracleCommand command2 = conn.CreateCommand();
                command2.CommandType = CommandType.Text;
                command2.CommandText = @"select phone,email   from accountcopy where accountid = :accountid";
                var accountid = new OracleParameter("accountid", OracleDbType.NVarchar2, 100);
                accountid.Value = model.Name;

                command2.Parameters.Add(accountid);

                using (var reader = command2.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["phone"] != DBNull.Value) model.Mobile = (string)reader["phone"];
                        if (reader["email"] != DBNull.Value) model.Email = (string)reader["email"];
                    }
                }
            }
            return model;
        }

        public string UpdateRoleByUserID(SFUser_XModel model, List<decimal> roles_US, List<decimal> roles_GJ)
        {
            try
            {
                using (OracleConnection conSelf = new OracleConnection(ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString))
                {
                    conSelf.Open();
                    if (model.Disabled == 1)
                    {
                        OracleCommand delcmd = new OracleCommand();
                        this.PrepareCommand(delcmd, conSelf, null, CommandType.Text, "delete from accountcopy where accountid = :accountid", new List<OracleParameter>(){
                    new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name,ParameterDirection.Input)
                    });
                        delcmd.ExecuteNonQuery();

                    }
                    else
                    {
                        OracleCommand updatecmd = new OracleCommand();
                        this.PrepareCommand(updatecmd, conSelf, null, CommandType.Text, "update AccountCopy set dept=:department,position=:position,phone=:phone,email=:email where accountid=:accountid", new List<OracleParameter>() { 
                    new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                    new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                    new OracleParameter("phone", OracleDbType.NVarchar2, 100, model.Mobile, ParameterDirection.Input),
                    new OracleParameter("email", OracleDbType.NVarchar2, 100, model.Email, ParameterDirection.Input),
                    new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name,ParameterDirection.Input)
                });
                        updatecmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return "自服务 系统错误，请重新点击保存，或联系管理员。（" + e.Message + ")";
            }
            try
            {
                string ARServer = ConfigurationManager.AppSettings["ARServer"];
                string ARUid = ConfigurationManager.AppSettings["ARUid"];
                string ARPwd = ConfigurationManager.AppSettings["ARPwd"];
                ARLoginContext context = new ARLoginContext(ARServer, ARUid, ARPwd);
                ARProxy<CTMPeople> ar = new ARProxy<CTMPeople>(context);
                var entryList = ar.GetEntryByQuery(string.Format("\'4\'=\"{0}\"", model.Name));
                if (entryList.Count > 0)
                {
                    var entry = entryList[0];
                    if (model.Disabled == 1)
                    {
                        entry.Status = CTMPeople.Status_Enum.Delete;
                        ar.SetEntry(entry);
                        ar.DeleteEntry(entry);

                    }
                    else
                    {
                        if (!string.IsNullOrWhiteSpace(model.Department)) entry.Department = model.Department;
                        if (!string.IsNullOrWhiteSpace(model.Position)) entry.JobTitle = model.Position;
                        if (!string.IsNullOrWhiteSpace(model.Mobile)) entry.PhoneNumberBusiness = model.Mobile;
                        if (!string.IsNullOrWhiteSpace(model.Email)) entry.InternetEmail = model.Email;

                        ar.SetEntry(entry);
                    }

                }
            }
            catch (Exception e)
            {
                return "ITSM 系统错误，请重新点击保存，或联系管理员。（" + e.Message + ")";
            }
            try
            {

                if (model.Disabled == 1)
                {
                    using (var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString))
                    {
                        conn.Open();
                        MySqlCommand command = new MySqlCommand();
                        command.Connection = conn;
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "delete_personbyname";//name of procdure
                        command.Parameters.Add(new MySqlParameter("PersonName", model.Name));
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return "认证服务 系统错误，请重新点击保存，或联系管理员。(" + e.Message + ")";
            }

            OracleTransaction trans = oda.BeginTransaction();
            try
            {
                oda.ExecuteScalar(sqlUpdateRoleByUserIDDUpdate, new List<OracleParameter>()
                {
                     new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                    new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                    new OracleParameter("disabled", OracleDbType.Decimal,  model.Disabled, ParameterDirection.Input),
                    new OracleParameter(){ ParameterName="IsAdmin",OracleDbType=OracleDbType.Decimal,Value=model.IsAdmin },
                    new OracleParameter(){ ParameterName="IsLeader",OracleDbType=OracleDbType.Decimal,Value=model.IsLeader },
                    new OracleParameter(){ ParameterName="AccessUS",OracleDbType=OracleDbType.Decimal,Value=model.AccessUS },
                    new OracleParameter(){ ParameterName="AccessITSM",OracleDbType=OracleDbType.Decimal,Value=model.AccessITSM},
                    new OracleParameter(){ ParameterName="AccessGJ",OracleDbType=OracleDbType.Decimal,Value=model.AccessGJ },
                    new OracleParameter(){ ParameterName="userID",OracleDbType=OracleDbType.Decimal,Value=model.ID }

                });
                oda.ExecuteScalar(sqlUpdateRoleByUserIDDelete, new List<OracleParameter>()
                {
                    new OracleParameter(){ ParameterName="userID",OracleDbType=OracleDbType.Decimal,Value=model.ID }
                });
                oda.ExecuteScalar(sqlUpdateRole_GJByUserIDDelete, new List<OracleParameter>()
                {
                    new OracleParameter(){ ParameterName="userID",OracleDbType=OracleDbType.Decimal,Value=model.ID }
                });
                foreach (var item in roles_US)
                {
                    oda.ExecuteScalar(sqlUpdateRoleByUserIDInsert, new List<OracleParameter>()
                {
                    new OracleParameter(){ ParameterName="userID",OracleDbType=OracleDbType.Decimal,Value=model.ID },
                    new OracleParameter(){ ParameterName="siteID",OracleDbType=OracleDbType.Decimal,Value=item }
                });
                }
                foreach (var item in roles_GJ)
                {
                    oda.ExecuteScalar(sqlUpdateRole_GJByUserIDInsert, new List<OracleParameter>()
                {
                    new OracleParameter(){ ParameterName="userID",OracleDbType=OracleDbType.Decimal,Value=model.ID  },
                    new OracleParameter(){ ParameterName="siteID",OracleDbType=OracleDbType.Decimal,Value=item }
                });
                }
                trans.Commit();
            }
            catch (Exception e) { 
                trans.Rollback();
                return "集中展现 系统错误，请重新点击保存，或联系管理员。（" + e.Message + ")";
            }
            return string.Empty;
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2)
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }

        public int ChangePassword(string login, string pass)
        {
            int status = 1;
            MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString);
            try
            {
                conn.Open();
                MySqlCommand command2 = conn.CreateCommand();
                command2.CommandType = CommandType.Text;
                command2.CommandText = @" update persons p set password=@password  where p.name=@name";
                var passP = new MySqlParameter("password", MySqlDbType.String, 100);
                passP.Value = pass;
                var nameP = new MySqlParameter("name", MySqlDbType.String, 100);
                nameP.Value = login;

                command2.Parameters.Add(passP);
                command2.Parameters.Add(nameP);

                command2.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                status = 0;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return status;
        }


        public int Create(SFUser_XModel model, out string resultStr)
        {
            int status = 0;


            using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString))
            {
                try
                {
                    //查询用户信息是否已存在
                    string selectStr1 = "select count(*) from USER_X where NAME = :NAME";
                    OracleCommand cmd1 = conn.CreateCommand();
                    List<OracleParameter> cmdParms = new List<OracleParameter>();
                    cmdParms.Add(new OracleParameter("NAME", OracleDbType.NVarchar2, model.Name, ParameterDirection.Input));
                    this.PrepareCommand(cmd1, conn, null, CommandType.Text, selectStr1, cmdParms);
                    int selectcount = Convert.ToInt32(cmd1.ExecuteScalar());
                    //如果已存在，那么更新该数据
                    if (selectcount > 0)
                    {
                        string updateStr = "update USER_X set DISPLAYNAME = :DISPLAYNAME, COMPANY = :COMPANY, DEPARTMENT = :DEPARTMENT, POSITION = :POSITION ,LASTMODIFIEDTIME=:LASTMODIFIEDTIME,DISABLED=:DISABLED where NAME = :NAME";
                        List<OracleParameter> updateParms = new List<OracleParameter>();
                        updateParms.Add(new OracleParameter("DISPLAYNAME", OracleDbType.NVarchar2, 100, (model.DisplayName == null ? "" : model.DisplayName), ParameterDirection.Input));
                        updateParms.Add(new OracleParameter("COMPANY", OracleDbType.NVarchar2, 100, (model.Company == null ? "" : model.Company), ParameterDirection.Input));
                        updateParms.Add(new OracleParameter("DEPARTMENT", OracleDbType.NVarchar2, 100, (model.Department == null ? "" : model.Department), ParameterDirection.Input));
                        updateParms.Add(new OracleParameter("POSITION", OracleDbType.NVarchar2, 100, (model.Position == null ? "" : model.Position), ParameterDirection.Input));
                        updateParms.Add(new OracleParameter("NAME", OracleDbType.NVarchar2, 100, model.Name, ParameterDirection.Input));
                        updateParms.Add(new OracleParameter("LASTMODIFIEDTIME", OracleDbType.Date, DateTime.Now, ParameterDirection.Input));
                        updateParms.Add(new OracleParameter("DISABLED", OracleDbType.Int32, 0, ParameterDirection.Input));
                        OracleCommand updatecmd = conn.CreateCommand();
                        this.PrepareCommand(updatecmd, conn, null, CommandType.Text, updateStr, updateParms);
                        updatecmd.ExecuteNonQuery();
                    }
                    //如果不存在，那么插入该数据
                    else
                    {
                        string insertStr = "insert into USER_X (NAME, DISPLAYNAME, COMPANY, DEPARTMENT, POSITION,DISABLED,LASTMODIFIEDTIME) values ( :NAME, :DISPLAYNAME, :COMPANY, :DEPARTMENT, :POSITION,:DISABLED,:LASTMODIFIEDTIME)";
                        List<OracleParameter> insertParms = new List<OracleParameter>();
                        insertParms.Add(new OracleParameter("DISPLAYNAME", OracleDbType.NVarchar2, 100, (model.DisplayName == null ? "" : model.DisplayName), ParameterDirection.Input));
                        insertParms.Add(new OracleParameter("COMPANY", OracleDbType.NVarchar2, 100, (model.Company == null ? "" : model.Company), ParameterDirection.Input));
                        insertParms.Add(new OracleParameter("DEPARTMENT", OracleDbType.NVarchar2, 100, (model.Department == null ? "" : model.Department), ParameterDirection.Input));
                        insertParms.Add(new OracleParameter("POSITION", OracleDbType.NVarchar2, 100, (model.Position == null ? "" : model.Position), ParameterDirection.Input));
                        insertParms.Add(new OracleParameter("NAME", OracleDbType.NVarchar2, 100, model.Name, ParameterDirection.Input));
                        insertParms.Add(new OracleParameter("LASTMODIFIEDTIME", OracleDbType.Date, DateTime.Now, ParameterDirection.Input));
                        insertParms.Add(new OracleParameter("DISABLED", OracleDbType.Int32, 0, ParameterDirection.Input));

                        OracleCommand insertcmd = conn.CreateCommand();
                        this.PrepareCommand(insertcmd, conn, null, CommandType.Text, insertStr, insertParms);
                        insertcmd.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    status = 1;
                    resultStr = e.Message;
                    return status;
                }

            }



            using (OracleConnection conSelf = new OracleConnection(ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString))
            {

                conSelf.Open();
                try
                {
                    //查询用户信息是否已存在
                    string selectStr = "select count(*) from accountcopy where accountid = :accountid";
                    List<OracleParameter> cmdParms = new List<OracleParameter>();
                    cmdParms.Add(new OracleParameter("accountid", OracleDbType.NVarchar2, model.Name, ParameterDirection.Input));
                    OracleCommand cmd = new OracleCommand();
                    this.PrepareCommand(cmd, conSelf, null, CommandType.Text, selectStr, cmdParms);
                    int selectcount = Convert.ToInt32(cmd.ExecuteScalar());
                    //如果已存在，那么更新该数据
                    if (selectcount > 0)
                    {
                        OracleCommand updatecmd = new OracleCommand();
                        this.PrepareCommand(updatecmd, conSelf, null, CommandType.Text, "update accountcopy set username = :username, company = :company, dept = :department, position = :position, email = :email,phone=:phone where accountid = :accountid", new List<OracleParameter>()
                    {
                    new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                    new OracleParameter("username", OracleDbType.NVarchar2, 100, model.DisplayName, ParameterDirection.Input),
                    new OracleParameter("company", OracleDbType.NVarchar2, 100, model.Company, ParameterDirection.Input),
                    new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                    new OracleParameter("phone", OracleDbType.NVarchar2, 100, model.Mobile, ParameterDirection.Input),
                    new OracleParameter("email", OracleDbType.NVarchar2, 100, model.Email, ParameterDirection.Input),
                    new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name,ParameterDirection.Input)
                       
                    });
                        updatecmd.ExecuteNonQuery();
                    }
                    //如果不存在，那么插入该数据
                    else
                    {
                        OracleCommand insertcmd = new OracleCommand();
                        this.PrepareCommand(insertcmd, conSelf, null, CommandType.Text, "insert into accountcopy (accountid, username, company, dept, position, email,phone) values ( :accountid, :username, :company, :department, :position, :email,:phone)",
                            new List<OracleParameter>()
                        {
                    new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                    new OracleParameter("username", OracleDbType.NVarchar2, 100, model.DisplayName, ParameterDirection.Input),
                    new OracleParameter("company", OracleDbType.NVarchar2, 100, model.Company, ParameterDirection.Input),
                    new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                    new OracleParameter("phone", OracleDbType.NVarchar2, 100, model.Mobile, ParameterDirection.Input),
                    new OracleParameter("email", OracleDbType.NVarchar2, 100, model.Email, ParameterDirection.Input),
                    new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name,ParameterDirection.Input)
                        });
                        insertcmd.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    status = 2;
                    resultStr = e.Message;
                    return status;
                }
            }


            try
            {
                string ARServer = ConfigurationManager.AppSettings["ARServer"];
                string ARUid = ConfigurationManager.AppSettings["ARUid"];
                string ARPwd = ConfigurationManager.AppSettings["ARPwd"];
                ARLoginContext context = new ARLoginContext(ARServer, ARUid, ARPwd);
                ARProxy<CTMPeople> ar = new ARProxy<CTMPeople>(context);
                var entryList = ar.GetEntryByQuery(string.Format("\'4\'=\"{0}\"", model.Name));
                CTMPeople entry = null;
                if (entryList.Count > 0)
                {
                    entry = entryList[0];
                    entry.Department = model.Department;
                    entry.JobTitle = model.Position;
                    entry.PhoneNumberBusiness = model.Mobile;
                    entry.InternetEmail = model.Email;
                    ar.SetEntry(entry);
                }
                else
                {
                    entry = new CTMPeople
                        {
                            Assigned_To = model.Name,
                            //电话需确定
                            Company = model.Company,
                            //Site值需确定
                            //Site = "",需要AR存在这个Site才能添加
                            //组织没有填写部门
                            //Organization = employee.DEPT,  需要AR存在这个组织才能添加
                            Department = model.Department,
                            FirstName = model.DisplayName.Substring(1),
                            LastName = model.DisplayName.Substring(0, 1),
                            FullName = model.DisplayName,
                            InternetEmail = model.Email,
                            JobTitle = model.Position,
                            Short_Description = ".",
                            Submitter = "Demo",
                            PhoneNumberBusiness = "###",
                            ClientType = CTMPeople.ClientType_Enum.office,
                            z1DPassword = "User@123",
                            Organization = model.Company,                            //only 1 means enabled
                            Status = CTMPeople.Status_Enum.Enabled
                        };
                    ar.CreateEntry(entry);
                }
            }
            catch (Exception e)
            {
                status = 3;
                resultStr = e.Message;
                return status;
            }


            using (var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString))
            {

                try
                {
                    conn.Open();
                    //var temp = conn.CreateCommand();
                    //temp.CommandType = CommandType.Text;
                    //temp.CommandText = "SET NAMES UTF8";
                    //temp.ExecuteNonQuery();
                    //MySqlCommand sqlSQL = conn.CreateCommand();
                    //sqlSQL.CommandType = CommandType.Text;
                    //sqlSQL.CommandText = "select orgcode from openldap.institutes where name=@name ";//like '@name%'";
                    //var pas = new MySqlParameter("name", MySqlDbType.String, 100);
                    //pas.Value = "福建省烟草专卖局(公司)本部"; //model.Company;
                    //sqlSQL.Parameters.Add(pas);
                    //string orgcode = string.Empty;
                    //using (var reader11 = sqlSQL.ExecuteReader())
                    //{
                    //    while (reader11.Read())
                    //    {
                    //        orgcode = (string)reader11["orgcode"];
                    //    }
                    //}

                    Dictionary<string, int> orgcodes = new Dictionary<string, int>()
                    {
                        {"泉州市烟草专卖局", 10350501 },
{"中国烟草福建进出口有限责任公司", 88880229      },
{"福州市烟草专卖局", 10350101 },
{"三明市烟草专卖局", 10350401 },
{"龙岩市烟草专卖局", 10350801 },
{"福建武夷烟叶有限公司", 88880227 },
{"福建省烟草专卖局", 88880218  },
{"漳州市烟草专卖局", 10350601 },
{"南平市烟草专卖局", 10350701 },
{"莆田市烟草专卖局", 10350301 },
{"福建烟草海晟投资管理有限公司", 99990229   },
{"宁德市烟草专卖局", 10350901 },
{"厦门市烟草专卖局", 10350201 },
{"福建三明金叶复烤有限公司", 88880228   }
                    };


                    MySqlCommand command = new MySqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "create_person";//name of procdure
                    command.Parameters.Add(new MySqlParameter("personCN", model.Name));
                    command.Parameters.Add(new MySqlParameter("personCode", Guid.NewGuid().ToString()));
                    command.Parameters.Add(new MySqlParameter("personuid", model.Name));
                    command.Parameters.Add(new MySqlParameter("personmail", null));
                    command.Parameters.Add(new MySqlParameter("parentCode", orgcodes[model.Company]));
                    command.Parameters.Add(new MySqlParameter("personPwd", model.Password));//pwd maybe encrypted
                    MySqlParameter param = new MySqlParameter("@entryid", model.ID);
                    param.Direction = ParameterDirection.Output;
                    command.Parameters.Add(param);
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    resultStr = e.Message;
                    status = 4;
                }
            }
            resultStr = string.Empty;
            return status;
        }


        public bool Exist(string loginName)
        {
            bool exists = false;
            MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString);
            try
            {
                conn.Open();
                MySqlCommand command2 = conn.CreateCommand();
                command2.CommandType = CommandType.Text;
                command2.CommandText = @" select count(*) from  persons p   where p.name=@name";
                var nameP = new MySqlParameter("name", MySqlDbType.String, 100);
                nameP.Value = loginName;

                command2.Parameters.Add(nameP);

                long count = (long)command2.ExecuteScalar();
                if (count >= 1) exists = true;
            }
            catch (Exception e)
            {
                exists = true;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return exists;
        }
    }
}
